using System;
using System.Text;
using System.Xml;
using System.Collections;

using gudusoft.gsqlparser;
using gudusoft.gsqlparser.Units;


namespace xmloutput
{
    class sqlserverstmt
    {
        public static string TMssqlOutputClauseXml(_TMssql_ParameterDecl pParameter)
        {

            XmlWriterSettings settings = new XmlWriterSettings();
            settings.OmitXmlDeclaration = true;
            settings.Indent = true;
            settings.IndentChars = ("    ");

            StringBuilder XmlText = new StringBuilder();
            using (XmlWriter writer = XmlWriter.Create(XmlText, settings))
            {
                // Write XML data.
                writer.WriteStartElement("parameter");
                writer.WriteAttributeString("name", pParameter._parameter_name.AsText);
                writer.WriteAttributeString("type", pParameter._datatype.AsText);
                if (pParameter._opt_declaration_expr != null)
                    if (((_TMssql_opt_declaration_expr)(pParameter._opt_declaration_expr))._stOut != null)
                    {
                        writer.WriteAttributeString("output", ((_TMssql_opt_declaration_expr)(pParameter._opt_declaration_expr))._stOut.AsText);
                    }
                writer.WriteEndElement();

                writer.Flush();
            }
            return XmlText.ToString();


        }

        public static string TMssqlOutputClauseXml(TMssqlOutputClause pOutputClause)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.OmitXmlDeclaration = true;
            settings.Indent = true;
            settings.IndentChars = ("    ");

            StringBuilder XmlText = new StringBuilder();
            using (XmlWriter writer = XmlWriter.Create(XmlText, settings))
            {
                // Write XML data.
                writer.WriteStartElement(pOutputClause.GetType().Name);
                writer.WriteCData(pOutputClause.AsText);
                writer.WriteEndElement();
                writer.Flush();
            }
            return XmlText.ToString();
        }

        public static string TLz_MssqlExecParamXml(TLz_MssqlExecParam pExecParam)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.OmitXmlDeclaration = true;
            settings.Indent = true;
            settings.IndentChars = ("    ");

            StringBuilder XmlText = new StringBuilder();
            using (XmlWriter writer = XmlWriter.Create(XmlText, settings))
            {
                // Write XML data.
                writer.WriteStartElement(pExecParam.GetType().Name);
                writer.WriteAttributeString("parametername", (pExecParam._stParamVar == null)? "" : pExecParam._stParamVar.AsText);
                writer.WriteAttributeString("outputdefault", (pExecParam._stOutput  == null) ? "" : pExecParam._stOutput.AsText);

                writer.WriteStartElement("parametervalue");
                writer.WriteRaw(basicnodexml.TLzCustomExpressionXml((TLzCustomExpression)pExecParam._ndParamValue));
                writer.WriteEndElement();

                writer.WriteEndElement();
                writer.Flush();
            }
            return XmlText.ToString();
        }

        public static string TMssqlExecuteXml(TMssqlExecute pExec)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.OmitXmlDeclaration = true;
            settings.Indent = true;
            settings.IndentChars = ("    ");

            StringBuilder XmlText = new StringBuilder();
            using (XmlWriter writer = XmlWriter.Create(XmlText, settings))
            {
                // Write XML data.
                writer.WriteStartElement("sqlstatement");
                writer.WriteAttributeString("type", pExec.SqlStatementType.ToString());
                writer.WriteAttributeString("exectype", pExec.ExecType.ToString());

                switch (pExec.ExecType)
                {
                    case TLzMssqlExecType.metExecSp:
                        writer.WriteStartElement("spname");
                        if (pExec.ModuleName != null)
                        {
                            writer.WriteRaw(basicnodexml.TLz_AttrXml(pExec.ModuleName));
                        }
                        else if (pExec.ModuleNameVar != null)
                        {
                            writer.WriteString(pExec.ModuleNameVar.AsText);
                        }
                        writer.WriteEndElement();

                        if (pExec.Parameters != null)
                        {
                            writer.WriteStartElement("parameters");
                            for (int i = 0; i < pExec.Parameters.Count(); i++)
                            {
                                writer.WriteRaw(TLz_MssqlExecParamXml((TLz_MssqlExecParam)(pExec.Parameters[i])));
                            }
                            writer.WriteEndElement();
                        }
                        
                        break;
                    case TLzMssqlExecType.metExecStringCmd:
                        Boolean ParsedCmd = false;
                        if (pExec.Parameters.Count() == 1)
                        {
                            TSourceToken st = (TSourceToken)pExec.Parameters[0];
                            if ((st.TokenType != TTokenType.ttSqlVar)&(st.AsText.Length > 4)){
                                string lcstr = st.AsText;
                                if (lcstr.StartsWith("N") || (lcstr.StartsWith("n")))
                                {
                                    lcstr = lcstr.Substring(1, lcstr.Length - 1);// remove N
                                }
                                lcstr = lcstr.Substring(1, lcstr.Length - 2);//remove ""
                                TGSqlParser parse = new TGSqlParser(TDbVendor.DbVMssql);
                                parse.SqlText.Text = lcstr;
                                int ret = parse.Parse();
                                if (parse.SqlStatements.Count() > 0)
                                {
                                    writer.WriteRaw(commonstmt.TStmtListXml(parse.SqlStatements));
                                    ParsedCmd = true;
                                }
                            }
                        }
                        if (!ParsedCmd){
                          writer.WriteCData(pExec.AsText);
                        }
                        break;
                    case TLzMssqlExecType .metExecStringCmdLinkServer:
                        writer.WriteCData(pExec.AsText);
                        break;
                    default:
                        break;
                }

                //writer.WriteRaw(commonstmt.TStmtListXml(pBlock.SqlStatements));
                writer.WriteEndElement();
                writer.Flush();
            }
            return XmlText.ToString();
        }

        public static string TMssqlSetXml(TMssqlSet pSet)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.OmitXmlDeclaration = true;
            settings.Indent = true;
            settings.IndentChars = ("    ");

            StringBuilder XmlText = new StringBuilder();
            using (XmlWriter writer = XmlWriter.Create(XmlText, settings))
            {
                // Write XML data.
                writer.WriteStartElement("sqlstatement");
                writer.WriteAttributeString("type", pSet.SqlStatementType.ToString());
                writer.WriteAttributeString("settype", pSet.setType.ToString());
                writer.WriteAttributeString("varname", (pSet.VarName != null) ? pSet.VarName.AsText : "");

                switch (pSet.setType)
                {
                    case TLzMssqlSetType.mstLocalVar:
                        writer.WriteStartElement("varvalue");
                        writer.WriteRaw(basicnodexml.TLzCustomExpressionXml(pSet.VarExpr));
                        writer.WriteEndElement();
                        break;
                    case TLzMssqlSetType.mstLocalVarCursor:
                        writer.WriteStartElement("varvalue");
                        writer.WriteRaw(commonstmt.TSelectSqlStatementXml(pSet.subquery));
                        writer.WriteEndElement();
                        break;
                    case TLzMssqlSetType.mstSetCmd:
                        writer.WriteCData(pSet.AsText);
                        break;
                    case TLzMssqlSetType.mstXmlMethod:
                        writer.WriteRaw(basicnodexml.TLz_FuncCallXml(pSet.XmlMethod));
                        break;
                    default:
                        break;

                }

                //writer.WriteRaw(commonstmt.TStmtListXml(pBlock.SqlStatements));
                writer.WriteEndElement();
                writer.Flush();
            }
            return XmlText.ToString();
        }

        public static string TMssqlBeginTranXml(TMssqlBeginTran pBeginTran)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.OmitXmlDeclaration = true;
            settings.Indent = true;
            settings.IndentChars = ("    ");

            StringBuilder XmlText = new StringBuilder();
            using (XmlWriter writer = XmlWriter.Create(XmlText, settings))
            {
                // Write XML data.
                writer.WriteStartElement("sqlstatement");
                writer.WriteAttributeString("type", pBeginTran.SqlStatementType.ToString());
                writer.WriteAttributeString("transactionname", (pBeginTran.TransactionNameToken != null)? pBeginTran.TransactionNameToken.AsText : "");
                writer.WriteAttributeString("isDistributed", pBeginTran.isDistributed.ToString());
                writer.WriteAttributeString("isWithMark", pBeginTran.isWithMark.ToString());
                writer.WriteAttributeString("WithMarkDescription", (pBeginTran.WithMarkDescriptionToken != null) ? pBeginTran.WithMarkDescriptionToken.AsText : "");

                writer.WriteEndElement();
                writer.Flush();
            }
            return XmlText.ToString();
        }

        public static string TMssqlReturnXml(TMssqlReturn pReturn)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.OmitXmlDeclaration = true;
            settings.Indent = true;
            settings.IndentChars = ("    ");

            StringBuilder XmlText = new StringBuilder();
            using (XmlWriter writer = XmlWriter.Create(XmlText, settings))
            {
                // Write XML data.
                writer.WriteStartElement("sqlstatement");
                writer.WriteAttributeString("type", pReturn.SqlStatementType.ToString());
                if (pReturn.expr != null)
                {
                    writer.WriteStartElement("returnvalue");
                    writer.WriteRaw(basicnodexml.TLzCustomExpressionXml((TLzCustomExpression)(pReturn.expr)));
                    writer.WriteEndElement();
                }
                //writer.WriteAttributeString("transactionname", (pCommit.transname != null) ? pCommit.transname.AsText : "");
                //writer.WriteAttributeString("workkeyword", (pCommit.stWork != null) ? pCommit.stWork.AsText : "");
                //writer.WriteAttributeString("trankeyword", (pCommit.stTran != null) ? pCommit.stTran.AsText : "");

                writer.WriteEndElement();
                writer.Flush();
            }
            return XmlText.ToString();
        }

        public static string TMssqlCommitXml(TMssqlCommit pCommit)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.OmitXmlDeclaration = true;
            settings.Indent = true;
            settings.IndentChars = ("    ");

            StringBuilder XmlText = new StringBuilder();
            using (XmlWriter writer = XmlWriter.Create(XmlText, settings))
            {
                // Write XML data.
                writer.WriteStartElement("sqlstatement");
                writer.WriteAttributeString("type", pCommit.SqlStatementType.ToString());
                writer.WriteAttributeString("transactionname", (pCommit.transname != null) ? pCommit.transname.AsText : "");
                writer.WriteAttributeString("workkeyword", (pCommit.stWork != null) ? pCommit.stWork.AsText: "");
                writer.WriteAttributeString("trankeyword", (pCommit.stTran != null) ? pCommit.stTran.AsText : "");

                writer.WriteEndElement();
                writer.Flush();
            }
            return XmlText.ToString();
        }

        public static string TMssqlRollbackXml(TMssqlRollback pRollback)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.OmitXmlDeclaration = true;
            settings.Indent = true;
            settings.IndentChars = ("    ");

            StringBuilder XmlText = new StringBuilder();
            using (XmlWriter writer = XmlWriter.Create(XmlText, settings))
            {
                // Write XML data.
                writer.WriteStartElement("sqlstatement");
                writer.WriteAttributeString("type", pRollback.SqlStatementType.ToString());
                writer.WriteAttributeString("transactionname", (pRollback.transname != null) ? pRollback.transname.AsText : "");
                writer.WriteAttributeString("workkeyword", (pRollback.stWork != null) ? pRollback.stWork.AsText : "");
                writer.WriteAttributeString("trankeyword", (pRollback.stTran != null) ? pRollback.stTran.AsText : "");

                writer.WriteEndElement();
                writer.Flush();
            }
            return XmlText.ToString();
        }

        public static string TMssqlDeallocateXml(TMssqlDeallocate pDeallocate)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.OmitXmlDeclaration = true;
            settings.Indent = true;
            settings.IndentChars = ("    ");

            StringBuilder XmlText = new StringBuilder();
            using (XmlWriter writer = XmlWriter.Create(XmlText, settings))
            {
                // Write XML data.
                writer.WriteStartElement("sqlstatement");
                writer.WriteAttributeString("type", pDeallocate.SqlStatementType.ToString());
                writer.WriteAttributeString("cursorname", pDeallocate.stCursorName.AsText);
                writer.WriteAttributeString("global", (pDeallocate.stGlobal != null) ? "true" : "false");

                writer.WriteEndElement();
                writer.Flush();
            }
            return XmlText.ToString();
        }

        public static string TMssqlDeclareXml(TMssqlDeclare pDeclare)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.OmitXmlDeclaration = true;
            settings.Indent = true;
            settings.IndentChars = ("    ");

            StringBuilder XmlText = new StringBuilder();
            using (XmlWriter writer = XmlWriter.Create(XmlText, settings))
            {
                // Write XML data.
                writer.WriteStartElement("sqlstatement");
                writer.WriteAttributeString("type", pDeclare.SqlStatementType.ToString());
                string declaretype = "generic";
                if (pDeclare.declaretype == 2)
                {
                    declaretype = "cursor";
                }
                writer.WriteAttributeString("declaretype", declaretype);

                if (pDeclare.declaretype == 1)
                {
                    writer.WriteStartElement("vars");
                    for (int i = 0; i < pDeclare.vars.Count(); i++)
                    {
                        writer.WriteRaw(_TMssql_declare_varXml((_TMssql_declare_var)(pDeclare.vars[i])));
                    }
                    writer.WriteEndElement();
                }
                else if (pDeclare.declaretype == 2)
                {

                    writer.WriteStartElement("cursorname");
                    writer.WriteString(pDeclare.cursorname.AsText);
                    writer.WriteEndElement();
                    writer.WriteRaw(commonstmt.TSelectSqlStatementXml(pDeclare.selectstmt));
                }



                //writer.WriteRaw(commonstmt.TStmtListXml(pBlock.SqlStatements));
                writer.WriteEndElement();
                writer.Flush();
            }
            return XmlText.ToString();
        }

        public static string _TMssql_declare_varXml(_TMssql_declare_var pVar)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.OmitXmlDeclaration = true;
            settings.Indent = true;
            settings.IndentChars = ("    ");

            StringBuilder XmlText = new StringBuilder();
            using (XmlWriter writer = XmlWriter.Create(XmlText, settings))
            {
                // Write XML data.
                writer.WriteStartElement(pVar.GetType().Name);
                writer.WriteAttributeString("vartype", pVar._vartype.ToString());
                writer.WriteAttributeString("varname", pVar._var_name.AsText);
                if ((pVar._vartype == TLzDeclareVarType.dvtGeneric) || (pVar._vartype == TLzDeclareVarType.dvtCursor))
                {
                    writer.WriteAttributeString("datatype", pVar._datatype.AsText);
                }
                else if (pVar._vartype == TLzDeclareVarType.dvtTable)
                {
                    writer.WriteRaw(basicnodexml.TLzFieldListXml(pVar.Fields));
                    if (pVar.TableConstraints.Count() > 0)
                    {
                        writer.WriteRaw(basicnodexml.TLzConstraintListXml(pVar.TableConstraints));
                    }
                }
                writer.WriteEndElement();
                writer.Flush();
            }
            return XmlText.ToString();
        }

        public static string TMssqlBlockXml(TMssqlBlock pBlock)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.OmitXmlDeclaration = true;
            settings.Indent = true;
            settings.IndentChars = ("    ");

            StringBuilder XmlText = new StringBuilder();
            using (XmlWriter writer = XmlWriter.Create(XmlText, settings))
            {
                // Write XML data.
                writer.WriteStartElement("sqlstatement");
                writer.WriteAttributeString("type", pBlock.SqlStatementType.ToString());
                writer.WriteRaw(commonstmt.TStmtListXml(pBlock.SqlStatements));
                writer.WriteEndElement();
                writer.Flush();
            }
            return XmlText.ToString();
        }

        public static string TMssqlIfElseXml(TMssqlIfElse pSql)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.OmitXmlDeclaration = true;
            settings.Indent = true;
            settings.IndentChars = ("    ");

            StringBuilder XmlText = new StringBuilder();
            using (XmlWriter writer = XmlWriter.Create(XmlText, settings))
            {
                // Write XML data.
                writer.WriteStartElement("sqlstatement");
                writer.WriteAttributeString("type", pSql.SqlStatementType.ToString());
                writer.WriteAttributeString("iswhile", pSql.iswhile.ToString());
                if (pSql.condition != null)
                {
                    writer.WriteStartElement("condition");
                    writer.WriteRaw(basicnodexml.TLzCustomExpressionXml((TLzCustomExpression)(pSql.condition)));
                    writer.WriteEndElement();
                }

                writer.WriteRaw(commonstmt.TSqlStatementXml(pSql.ifstmt._sqlstmt));

                if (pSql.elsestmt != null)
                {
                    writer.WriteStartElement("elsestmt");
                    writer.WriteRaw(commonstmt.TSqlStatementXml(pSql.elsestmt._sqlstmt));
                    writer.WriteEndElement();
                }
                writer.WriteEndElement();
                writer.Flush();
            }
            return XmlText.ToString();
        }

        public static string TMssqlCreateTriggerXml(TMssqlCreateTrigger pSql)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.OmitXmlDeclaration = true;
            settings.Indent = true;
            settings.IndentChars = ("    ");
            settings.ConformanceLevel = ConformanceLevel.Fragment;

            StringBuilder XmlText = new StringBuilder();
            using (XmlWriter writer = XmlWriter.Create(XmlText, settings))
            {
                // Write XML data.
                writer.WriteStartElement("sqlstatement");
                writer.WriteAttributeString("type", pSql.SqlStatementType.ToString());

                string firemode = "for";
                if (pSql.FireMode == 1 ){
                    firemode = "after";
                } else if (pSql.FireMode == 2){
                    firemode = "insted of";
                }
                writer.WriteAttributeString("firemode", firemode);

                string dmltypes = "";
                foreach (TSourceToken st in pSql.DmlTypes)
                {
                    dmltypes = dmltypes + " " + st.AsText;
                }

                writer.WriteAttributeString("DmlTypes", dmltypes);

                writer.WriteStartElement("TriggerName");
                writer.WriteRaw(basicnodexml.TLz_AttrXml(pSql.TriggerName));
                writer.WriteEndElement();

                writer.WriteStartElement("OnTableName");
                writer.WriteRaw(basicnodexml.TLz_AttrXml(pSql.OnTableName));
                writer.WriteEndElement();


                writer.WriteRaw(commonstmt.TStmtListXml(pSql.SqlStatements));

                writer.WriteEndElement();
                writer.Flush();
            }
            return XmlText.ToString();
        }

        public static string TMssqlCreateProcedureXml(TMssqlCreateProcedure pSql)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.OmitXmlDeclaration = true;
            settings.Indent = true;
            settings.IndentChars = ("    ");
            settings.ConformanceLevel = ConformanceLevel.Fragment;

            StringBuilder XmlText = new StringBuilder();
            using (XmlWriter writer = XmlWriter.Create(XmlText, settings))
            {
                // Write XML data.
                writer.WriteStartElement("sqlstatement");
                writer.WriteAttributeString("type", pSql.SqlStatementType.ToString());

                writer.WriteStartElement("procedurename");
                writer.WriteRaw(basicnodexml.TLz_AttrXml(pSql.procedurename));
                writer.WriteEndElement();

                writer.WriteStartElement("parameters");
                if (pSql.procedure_parameters_declaration != null)
                {
                    for (int i = 0; i < pSql.procedure_parameters_declaration.Count(); i++)
                    {
                        ///writer.WriteStartElement("parameter");
                        
                        _TMssql_ParameterDecl p = (_TMssql_ParameterDecl)pSql.procedure_parameters_declaration[i];
                        writer.WriteRaw(TMssqlOutputClauseXml(p));

                        //writer.WriteAttributeString("name", p._parameter_name.AsText);
                        //writer.WriteAttributeString("type", p._datatype.AsText);
                        //writer.WriteEndElement();
                    }
                }
                writer.WriteEndElement();

                writer.WriteRaw(commonstmt.TStmtListXml(pSql.SqlStatements));

                writer.WriteEndElement();
                writer.Flush();
            }
            return XmlText.ToString();
        }

        public static string TMssqlCreateFunctionXml(TMssqlCreateFunction pSql)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.OmitXmlDeclaration = true;
            settings.Indent = true;
            settings.IndentChars = ("    ");
            settings.ConformanceLevel = ConformanceLevel.Fragment;

            StringBuilder XmlText = new StringBuilder();
            using (XmlWriter writer = XmlWriter.Create(XmlText, settings))
            {
                // Write XML data.
                writer.WriteStartElement("sqlstatement");
                writer.WriteAttributeString("type", pSql.SqlStatementType.ToString());

                writer.WriteStartElement("functionname");
                writer.WriteRaw(basicnodexml.TLz_AttrXml(pSql.functionname));
                writer.WriteEndElement();
                
                writer.WriteElementString("functiontype", pSql.functiontype.ToString());

                writer.WriteStartElement("parameters");
                if (pSql.procedure_parameters_declaration != null)
                {
                    for (int i = 0; i < pSql.procedure_parameters_declaration.Count(); i++)
                    {
                        //writer.WriteStartElement("parameter");
                        _TMssql_ParameterDecl p = (_TMssql_ParameterDecl)pSql.procedure_parameters_declaration[i];
                        writer.WriteRaw(TMssqlOutputClauseXml(p));

                        //writer.WriteAttributeString("name", p._parameter_name.AsText);
                        //writer.WriteAttributeString("type", p._datatype.AsText);
                        //writer.WriteEndElement();
                    }
                }
                writer.WriteEndElement();

                switch (pSql.functiontype)
                {
                    case TLzUDFType.uftScalar:
                        writer.WriteElementString("returntype", pSql.returntype.AsText);
                        if (pSql.blockstmt != null)
                        {
                            writer.WriteRaw(commonstmt.TStmtListXml(pSql.blockstmt.SqlStatements));
                        }

                        break;
                    case TLzUDFType.uftInlineTableValued:
                        if (pSql.returnstmt != null)
                        {
                            TLzCustomExpression lcexpr = (TLzCustomExpression)(pSql.returnstmt.expr);

                            if (lcexpr.oper == TLzOpType.Expr_subquery)
                            {
                                //return value is a subquery
                                TSelectSqlStatement select = (TSelectSqlStatement)lcexpr.lexpr;
                                writer.WriteStartElement("returntable");
                                writer.WriteRaw(commonstmt.TSelectSqlStatementXml(select));
                                writer.WriteEndElement();
                            }
                        }
                        break;
                    case TLzUDFType.uftMultiStatementTableValued:
                        writer.WriteStartElement("returntable");
                        writer.WriteAttributeString("tablevarname", pSql.returnTableVar.AsText);
                        writer.WriteRaw(basicnodexml.TLzFieldListXml(pSql.Table.Fields));
                        //foreach (TLzField field in pSql.Table.Fields)
                        //{
                        //    writer.WriteStartElement("column");
                        //    writer.WriteAttributeString("name", field.FieldName);
                        //    writer.WriteAttributeString("type", field.FieldDataType.AsText);
                        //    writer.WriteEndElement();
                        //}
                        writer.WriteRaw(commonstmt.TStmtListXml(pSql.blockstmt.SqlStatements));
                        writer.WriteEndElement();
                        break;

                }
                writer.WriteEndElement();
                writer.Flush();
            }
            return XmlText.ToString();
        }
    }
}
